{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "72c5bfbe",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/peremartra/Large-Language-Model-Notebooks-Course/blob/main/1-Introduction%20to%20LLMs%20with%20OpenAI/1_2-Easy_NL2SQL_Gradio.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "MDJ3_BRhasUG",
   "metadata": {
    "id": "MDJ3_BRhasUG"
   },
   "source": [
    "<div>\n",
    "<h1>Large Language Models Projects</h1>\n",
    "    <h3>Apply and Implement Strategies for Large Language Models</h3>\n",
    "    <h2>1.2-Create a simple Natural Language to SQL with OpenAI and Gradio.</h2>\n",
    "    \n",
    "</div>\n",
    "\n",
    "by [Pere Martra](https://www.linkedin.com/in/pere-martra/)\n",
    "<hr>\n",
    "\n",
    "Models: gpt-3.5-turbo / gpt-4o-mini\n",
    "\n",
    "Colab Environment: CPU\n",
    "\n",
    "Keys:\n",
    "* NL2SQL\n",
    "* Gradio\n",
    "* Code Generation\n",
    "* Prompt Hardening.\n",
    "\n",
    "Article related: [Create a Natural Language to SQL Solution with OpenAI and Gradio](https://pub.towardsai.net/first-nl2sql-chat-with-openai-and-gradio-b1de0d6541b4?sk=7e0346b93130e70574645d5d390adfe9)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c23b4dd1",
   "metadata": {
    "id": "c23b4dd1"
   },
   "source": [
    "# SQL Generator\n",
    "A sample of how to build a translator from natural language to SQL:\n",
    "\n",
    "* GPT 35 / gpt-4o-mini\n",
    "* OpenAI\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a1d00720",
   "metadata": {
    "id": "a1d00720"
   },
   "outputs": [],
   "source": [
    "!pip install -q openai==1.1.1\n",
    "!pip install -q gradio==4.41.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a03f026a",
   "metadata": {
    "id": "a03f026a"
   },
   "outputs": [],
   "source": [
    "import openai\n",
    "import panel as pn\n",
    "from getpass import getpass\n",
    "import gradio as gr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9uS06-2jbNs1",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "9uS06-2jbNs1",
    "outputId": "c991fc4f-675f-4c88-cd08-b5fcae830f18"
   },
   "outputs": [],
   "source": [
    "openai.api_key=getpass(\"OpenAI API Key: \")\n",
    "#model = \"gpt-3.5-turbo\"\n",
    "model = \"gpt-4o-mini\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "xEx1chXxXE2h",
   "metadata": {
    "id": "xEx1chXxXE2h"
   },
   "outputs": [],
   "source": [
    "context = [ {'role':'system', 'content':\"\"\"\n",
    "you are a bot to assist in create SQL commands, all your answers should start with\n",
    "this is your SQL, and after that an SQL that can do what the user request.\n",
    "\n",
    "Your SQL Database is composed by some tables.\n",
    "Try to Maintain the SQL order simple.\n",
    "Just after the SQL add a simple and concise text explaining how it works.\n",
    "If the user ask for something that can not be answered with information from the DB\n",
    "just answer something nice and simple, maximum 10 words, asking him for a new question that\n",
    "can be solved with SQL.\n",
    "\"\"\"} ]\n",
    "\n",
    "context.append( {'role':'system', 'content':\"\"\"\n",
    "first table:\n",
    "{\n",
    "  \"tableName\": \"employees\",\n",
    "  \"fields\": [\n",
    "    {\n",
    "      \"nombre\": \"ID_usr\",\n",
    "      \"tipo\": \"int\"\n",
    "    },\n",
    "    {\n",
    "      \"nombre\": \"name\",\n",
    "      \"tipo\": \"string\"\n",
    "    }\n",
    "  ]\n",
    "}\n",
    "\"\"\"\n",
    "})\n",
    "\n",
    "context.append( {'role':'system', 'content':\"\"\"\n",
    "second table:\n",
    "{\n",
    "  \"tableName\": \"salary\",\n",
    "  \"fields\": [\n",
    "    {\n",
    "      \"nombre\": \"ID_usr\",\n",
    "      \"type\": \"int\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"year\",\n",
    "      \"type\": \"date\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"salary\",\n",
    "      \"type\": \"float\"\n",
    "    }\n",
    "  ]\n",
    "}\n",
    "\"\"\"\n",
    "})\n",
    "\n",
    "context.append( {'role':'system', 'content':\"\"\"\n",
    "third table:\n",
    "{\n",
    "  \"tablename\": \"studies\",\n",
    "  \"fields\": [\n",
    "    {\n",
    "      \"name\": \"ID\",\n",
    "      \"type\": \"int\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"ID_usr\",\n",
    "      \"type\": \"int\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"educational level\",\n",
    "      \"type\": \"int\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"Institution\",\n",
    "      \"type\": \"string\"\n",
    "    },\n",
    "    {\n",
    "      \"name\": \"Years\",\n",
    "      \"type\": \"date\"\n",
    "    }\n",
    "    {\n",
    "      \"name\": \"Speciality\",\n",
    "      \"type\": \"string\"\n",
    "    }\n",
    "  ]\n",
    "}\n",
    "\"\"\"\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77eac86d",
   "metadata": {
    "id": "77eac86d"
   },
   "outputs": [],
   "source": [
    "def continue_conversation(messages, temperature=0):\n",
    "    response = openai.chat.completions.create(\n",
    "        model=model,\n",
    "        messages=messages,\n",
    "        temperature=temperature,\n",
    "    )\n",
    "    return response.choices[0].message.content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "nDrtcJviXjwb",
   "metadata": {
    "id": "nDrtcJviXjwb"
   },
   "outputs": [],
   "source": [
    "#The function that Gradio will use.\n",
    "def gradio_chat(message, history):\n",
    "    #Add the instructions to the prompt.\n",
    "    history_chat = context\n",
    "\n",
    "    #Add the history that Gradio send to us.\n",
    "    for user, assistant in history:\n",
    "        history_chat.append({\"role\":\"user\", \"content\":user})\n",
    "        history_chat.append({\"role\":\"assistant\", \"content\":assistant})\n",
    "\n",
    "    #Add the las user message.\n",
    "    history_chat.append({\"role\":\"user\", \"content\":message})\n",
    "    history_chat.append({'role':'system', 'content':f\"\"\"Only return SQL Orders.\n",
    "     If you can't return and SQL order, say sorry, and ask, politely but concisely, for a new question.\"\"\"})\n",
    "\n",
    "    #Call OpenAI and return the response.\n",
    "    return continue_conversation(history_chat, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "myN9PronasVJ",
   "metadata": {
    "id": "myN9PronasVJ"
   },
   "outputs": [],
   "source": [
    "#Customized gradio textbox.\n",
    "InputText = gr.Textbox(label=\"order\", info=\"Talk with DB\", scale= 6)\n",
    "examples=[\"Who is the highest-paid employee?\", \"How many employes with degrees do we have?\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ZA2QmOWfavry",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 645
    },
    "id": "ZA2QmOWfavry",
    "outputId": "a591418f-b89c-4a3d-bc46-84b2b1024eb9"
   },
   "outputs": [],
   "source": [
    "gr.ChatInterface(gradio_chat,\n",
    "                 textbox=InputText,\n",
    "                 retry_btn=None,\n",
    "                 undo_btn=None,\n",
    "                 title=\"SQL Generator\",\n",
    "                 examples=examples,\n",
    "                submit_btn=\"Get My SQL\").launch()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "MSn19fgAcpCa",
   "metadata": {
    "id": "MSn19fgAcpCa"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
